#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
-- 线索申诉信息表：dim_customer_appeal
DROP TABLE IF EXISTS edu_online_dwd.dim_customer_appeal;
CREATE TABLE edu_online_dwd.dim_customer_appeal
(
    id                             INT COMMENT '主键',
    customer_relationship_first_id INT COMMENT '第一条客户关系id',
    employee_id                    INT COMMENT '申诉人',
    employee_name                  STRING COMMENT '申诉人姓名',
    employee_department_id         INT COMMENT '申诉人部门',
    employee_tdepart_id            INT COMMENT '申诉人所属部门',
    appeal_status                  INT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    audit_id                       INT COMMENT '稽核人id',
    audit_name                     STRING COMMENT '稽核人姓名',
    audit_department_id            INT COMMENT '稽核人所在部门',
    audit_department_name          STRING COMMENT '稽核人部门名称',
    audit_date_time                STRING COMMENT '稽核时间',
    create_date_time               DATE COMMENT '创建时间（申诉时间）',
    update_date_time               DATE COMMENT '更新时间',
    deleted                        INT COMMENT '删除标志位',
    tenant                         INT COMMENT '租户',
    end_date                       STRING COMMENT '拉链结束日期'
)COMMENT '线索申诉信息表'
PARTITIONED BY (start_date STRING COMMENT '拉链开始日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


-- 客户静态信息表：dim_customer
DROP TABLE IF EXISTS edu_online_dwd.dim_customer;
CREATE TABLE edu_online_dwd.dim_customer
(
    id                       INT COMMENT '客户id',
    customer_relationship_id INT COMMENT '当前意向id',
    create_date_time         DATE COMMENT '创建时间',
    update_date_time         DATE COMMENT '更新时间',
    deleted                  BOOLEAN COMMENT '是否被删除（禁用）',
    name                     STRING COMMENT '姓名',
    idcard                   STRING COMMENT '身份证号',
    birth_year               INT COMMENT '出生年份',
    gender                   STRING COMMENT '性别',
    phone                    STRING COMMENT '手机号',
    wechat                   STRING COMMENT '微信',
    qq                       STRING COMMENT 'qq号',
    email                    STRING COMMENT '邮箱',
    area                     STRING COMMENT '所在区域',
    leave_school_date        DATE COMMENT '离校时间',
    graduation_date          DATE COMMENT '毕业时间',
    bxg_student_id           STRING COMMENT '博学谷学员ID，可能未关联到，不存在',
    creator                  INT COMMENT '创建人ID',
    origin_type              STRING COMMENT '数据来源',
    origin_channel           STRING COMMENT '来源渠道',
    tenant                   INT COMMENT '租户',
    md_id                    INT COMMENT '中台id',
    end_date                 STRING COMMENT '拉链结束日期'
)COMMENT '客户静态信息表'
PARTITIONED BY (start_date STRING COMMENT '拉链开始日期')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


-- 校区信息表：dim_itcast_school
DROP TABLE IF EXISTS edu_online_dwd.dim_itcast_school;
CREATE TABLE edu_online_dwd.dim_itcast_school
(
    id               INT COMMENT '校区id',
    create_date_time DATE COMMENT '创建时间',
    update_date_time DATE COMMENT '更新时间',
    deleted          BOOLEAN COMMENT '是否被删除（禁用）',
    name             STRING COMMENT '校区名称',
    code             STRING COMMENT '校区编码',
    tenant           INT COMMENT '租户'
)COMMENT '校区信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


-- 学科信息表：dim_itcast_subject
DROP TABLE IF EXISTS edu_online_dwd.dim_itcast_subject;
CREATE TABLE edu_online_dwd.dim_itcast_subject
(
    id               INT COMMENT '学科id',
    create_date_time DATE COMMENT '创建时间',
    update_date_time DATE COMMENT '更新时间',
    deleted          BOOLEAN COMMENT '是否被删除（禁用）',
    name             STRING COMMENT '学科名称',
    code             STRING COMMENT '学科编码',
    tenant           INT COMMENT '租户'
)COMMENT '学科信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


-- 员工信息表：dim_employee
DROP TABLE IF EXISTS edu_online_dwd.dim_employee;
CREATE TABLE edu_online_dwd.dim_employee
(
    id                  INT COMMENT '员工id',
    email               STRING COMMENT '公司邮箱，OA登录账号',
    real_name           STRING COMMENT '员工的真实姓名',
    phone               STRING COMMENT '手机号，目前还没有使用；隐私问题OA接口没有提供这个属性，',
    department_id       STRING COMMENT 'OA中的部门编号，有负值',
    department_name     STRING COMMENT 'OA中的部门名',
    remote_login        BOOLEAN COMMENT '员工是否可以远程登录',
    job_number          STRING COMMENT '员工工号',
    cross_school        BOOLEAN COMMENT '是否有跨校区权限',
    last_login_date     DATE COMMENT '最后登录日期',
    creator             INT COMMENT '创建人id',
    create_date_time    DATE COMMENT '创建时间',
    update_date_time    DATE COMMENT '最后更新时间',
    deleted             BOOLEAN COMMENT '是否被删除（禁用）',
    scrm_department_id  INT COMMENT 'SCRM内部部门id',
    leave_office        BOOLEAN COMMENT '离职状态',
    leave_office_time   DATE COMMENT '离职时间',
    reinstated_time     DATE COMMENT '复职时间',
    superior_leaders_id INT COMMENT '上级领导ID',
    tdepart_id          INT COMMENT '直属部门',
    tenant              INT COMMENT '租户',
    ems_user_name       STRING COMMENT 'ems用户名称'
)COMMENT '员工信息表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');


-- 员工部门表：dim_scrm_department
DROP TABLE IF EXISTS edu_online_dwd.dim_scrm_department;
CREATE TABLE edu_online_dwd.dim_scrm_department
(
    id               INT COMMENT '员工部门id',
    name             STRING COMMENT '部门名称',
    parent_id        INT COMMENT '父部门id',
    create_date_time DATE COMMENT '创建时间',
    update_date_time DATE COMMENT '更新时间',
    deleted          BOOLEAN COMMENT '删除标志',
    id_path          STRING COMMENT '编码全路径',
    tdepart_code     INT COMMENT '直属部门',
    creator          STRING COMMENT '创建者',
    depart_level     INT COMMENT '部门层级',
    depart_sign      INT COMMENT '部门标志，暂时默认1',
    depart_line      INT COMMENT '业务线，存储业务线编码',
    depart_sort      INT COMMENT '排序字段',
    disable_flag     INT COMMENT '禁用标志',
    tenant           INT COMMENT '租户'
)COMMENT '员工部门表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');
"